#!/usr/bin/perl

######################################################################
#
# mdb2sml.pl
#
# $Id: mdb2sml.pl 130 2014-01-20 15:54:12Z drj826@gmail.com $
#
# Convert PPARES membership database (CSV format) to Semantic Markup
# Language (SML).
#
#---------------------------------------------------------------------
# USAGE (execution)
#
#     perl mdb2sml.pl --skip 1 --order 10 -- data.csv
#
#         --order   | -o => order by values in specified column
#         --skip    | -s => comma separated list of lines to skip
#
######################################################################

use strict;
use Text::CSV;
use Getopt::Long qw(:config no_ignore_case);

my $order;                              # column on which to sort records
my @skip;                               # line numbers to skip

GetOptions
  (
   "order|o=s" => \$order,
   "skip|s=s"  => \@skip,
  );

@skip = split(',',join(',',@skip));

my %skip_line;                          # lines to skip
foreach my $line (@skip) {
  $skip_line{$line} = 1;
}

my $file = shift;                       # CSV file to parse
my $data = read_csv_file($file);

my @sorted;

if ( $order )
  {
    @sorted = sort by_order @{ $data };
  }

else
  {
    @sorted = @{ $data };
  }

my $row = 0;
my $ord = 'even';

foreach my $record ( @sorted )
  {
    my $callsign    = $record->[9];
    my $lastname    = $record->[19];
    my $firstname   = $record->[17];
    my $middlename  = $record->[18];

    #-----------------------------------------------------------------
    # section header
    #
    print <<"END_OF_TEXT";
*** $callsign - $lastname, $firstname $middlename

index:: $callsign

END_OF_TEXT

    #-----------------------------------------------------------------
    # identification
    #
    my $idcard      = 'does not have a PPARES ID card';
    if ($record->[125])
      {
	$idcard = "has PPARES ID card";
      }

    print <<"END_OF_TEXT";
$firstname $middlename $lastname, $record->[10], $idcard

END_OF_TEXT

    #-----------------------------------------------------------------
    # last update
    #
    print "Last update: $record->[3]\n\n";

    #-----------------------------------------------------------------
    # last update
    #
    print "Initial registration: $record->[2]\n\n";

    #-----------------------------------------------------------------
    # staff position
    #
    if ( $record->[20] )
      {
	print <<"END_OF_TEXT";
Staff Position: $record->[21]

END_OF_TEXT
      }

    #-----------------------------------------------------------------
    # previous staff position
    #
    if ( $record->[22] )
      {
	print <<"END_OF_TEXT";
Previous Staff Position: $record->[22]

END_OF_TEXT
      }

    #-----------------------------------------------------------------
    # deployment group
    #
    if ( $record->[119] )
      {
	print <<"END_OF_TEXT";
Deployment Group: $record->[119]

END_OF_TEXT
      }

    #-----------------------------------------------------------------
    # address
    #
    my $address = '';
    if ($record->[23])
      {
	$address .= "$record->[23] ";
      }

    if ($record->[24])
      {
	$address .= "$record->[24] ";
      }

    if ($record->[25])
      {
	$address .= "$record->[25] ";
      }

    if ($record->[26])
      {
	$address .= "$record->[26] ";
      }

    if ($record->[27])
      {
	$address .= "$record->[27] ";
      }

    my $crossstreet = '';
    if ($record->[29])
      {
	$crossstreet = "(near $record->[29])";
      }

    my $county = '';
    if ($record->[28])
      {
	$county = "$record->[28] County";
      }

    my $district = '';
    if ($record->[30])
      {
	$district = "District $record->[30]";
      }

    my $location = '';
    if ( $address )
      {
	$location .= "$address "
      }

    if ( $crossstreet )
      {
	$location .= "$crossstreet "
      }

    if ( $county )
      {
	$location .= "$county "
      }

    if ( $district )
      {
	$location .= "$district "
      }

    print "$location\n\n";

    #-----------------------------------------------------------------
    # phone 1
    #
    if ($record->[31])
      {
	my $phone    = $record->[31];
	my $type     = $record->[32];
	my $unlisted = $record->[33];

	if ( $type )
	  {
	    $phone .= " ($type) ";
	  }

	if ( $unlisted )
	  {
	    $phone .= "UNLISTED";
	  }

	print "Phone 1: $phone\n\n";
      }

    #-----------------------------------------------------------------
    # phone 2
    #
    if ($record->[34])
      {
	my $phone    = $record->[34];
	my $type     = $record->[35];
	my $unlisted = $record->[36];

	if ( $type )
	  {
	    $phone .= " ($type) ";
	  }

	if ( $unlisted )
	  {
	    $phone .= "UNLISTED";
	  }

	print "Phone 2: $phone\n\n";
      }

    #-----------------------------------------------------------------
    # phone 3
    #
    if ($record->[37])
      {
	my $phone    = $record->[37];
	my $type     = $record->[38];
	my $unlisted = $record->[39];

	if ( $type )
	  {
	    $phone .= " ($type) ";
	  }

	if ( $unlisted )
	  {
	    $phone .= "UNLISTED";
	  }

	print "Phone 3: $phone\n\n";
      }

    #-----------------------------------------------------------------
    # sms
    #
    if ($record->[40] and $record->[41])
      {
	my $phone   = $record->[40];
	my $carrier = $record->[41];

	$phone =~ s/[\)\(\-\ ]//g;

	print "SMS: $phone$carrier\n\n";
      }

    #-----------------------------------------------------------------
    # email 1
    #
    if ($record->[42])
      {
	my $email    = $record->[42];
	my $type     = $record->[43];
	my $unlisted = $record->[44];

	if ( $type )
	  {
	    $email .= " ($type) ";
	  }

	if ( $unlisted )
	  {
	    $email .= "UNLISTED";
	  }

	print "Email 1: $email\n\n";
      }

    #-----------------------------------------------------------------
    # email 2
    #
    if ($record->[45])
      {
	my $email    = $record->[45];
	my $type     = $record->[46];
	my $unlisted = $record->[47];

	if ( $type )
	  {
	    $email .= " ($type) ";
	  }

	if ( $unlisted )
	  {
	    $email .= "UNLISTED";
	  }

	print "Email 2: $email\n\n";
      }

    #-----------------------------------------------------------------
    # email 3
    #
    if ($record->[48])
      {
	my $email    = $record->[48];
	my $type     = $record->[49];
	my $unlisted = $record->[50];

	if ( $type )
	  {
	    $email .= " ($type) ";
	  }

	if ( $unlisted )
	  {
	    $email .= "UNLISTED";
	  }

	print "Email 3: $email\n\n";
      }

    #-----------------------------------------------------------------
    # notifications
    #
    my $notify_emerg = '';
    if ($record->[55])
      {
	$notify_emerg = ':green: yes';
      }

    else
      {
	$notify_emerg = ':red: no';
      }

    my $notify_ares = '';
    if ($record->[52])
      {
	$notify_ares = ':green: yes';
      }

    else
      {
	$notify_ares = ':red: no';
      }

    my $notify_exer = '';
    if ($record->[53])
      {
	$notify_exer = ':green: yes';
      }

    else
      {
	$notify_exer = ':red: no';
      }

    my $notify_wx = '';
    if ($record->[51])
      {
	$notify_wx = ':green: yes';
      }

    else
      {
	$notify_wx = ':red: no';
      }

    my $notify_pse = '';
    if ($record->[54])
      {
	$notify_pse = ':green: yes';
      }

    else
      {
	$notify_pse = ':red: no';
      }

    print <<"END_OF_TEXT";
: Notify of:

: Emergencies

: ARES

: Exercises

: WX Events

: PS Events

:

---

:

$notify_emerg

$notify_ares

$notify_exer

$notify_wx

$notify_pse

:

---

END_OF_TEXT

    #-----------------------------------------------------------------
    # general availability
    #
    my $avail_247 = '';
    if ($record->[56])
      {
	$avail_247 = ':green: yes';
      }

    else
      {
	$avail_247 = ':red: no';
      }

    my $avail_mfd = '';
    if ($record->[57])
      {
	$avail_mfd = ':green: yes';
      }

    else
      {
	$avail_mfd = ':red: no';
      }

    my $avail_mfe = '';
    if ($record->[58])
      {
	$avail_mfe = ':green: yes';
      }

    else
      {
	$avail_mfe = ':red: no';
      }

    my $avail_ssd = '';
    if ($record->[59])
      {
	$avail_ssd = ':green: yes';
      }

    else
      {
	$avail_ssd = ':red: no';
      }

    my $avail_sse = '';
    if ($record->[60])
      {
	$avail_sse = ':green: yes';
      }

    else
      {
	$avail_sse = ':red: no';
      }

    print <<"END_OF_TEXT";
: Gen Availability:

: 24/7

: M-F day

: M-F eve

: S-S day

: S-S eve

:

---

:

$avail_247

$avail_mfd

$avail_mfe

$avail_ssd

$avail_sse

:

---

END_OF_TEXT

    #-----------------------------------------------------------------
    # wx availability
    #
    my $wxavail_247 = '';
    if ($record->[61])
      {
	$wxavail_247 = ':green: yes';
      }

    else
      {
	$wxavail_247 = ':red: no';
      }

    my $wxavail_mfd = '';
    if ($record->[62])
      {
	$wxavail_mfd = ':green: yes';
      }

    else
      {
	$wxavail_mfd = ':red: no';
      }

    my $wxavail_mfe = '';
    if ($record->[63])
      {
	$wxavail_mfe = ':green: yes';
      }

    else
      {
	$wxavail_mfe = ':red: no';
      }

    my $wxavail_ssd = '';
    if ($record->[64])
      {
	$wxavail_ssd = ':green: yes';
      }

    else
      {
	$wxavail_ssd = ':red: no';
      }

    my $wxavail_sse = '';
    if ($record->[65])
      {
	$wxavail_sse = ':green: yes';
      }

    else
      {
	$wxavail_sse = ':red: no';
      }

    print <<"END_OF_TEXT";
: WX Availability:

: 24/7

: M-F day

: M-F eve

: S-S day

: S-S eve

:

---

:

$wxavail_247

$wxavail_mfd

$wxavail_mfe

$wxavail_ssd

$wxavail_sse

:

---

END_OF_TEXT

    #-----------------------------------------------------------------
    # emcomm training
    #
    my $ec_001 = '';
    if ($record->[66])
      {
	$ec_001 = ":green: $record->[67]";
      }

    else
      {
	$ec_001 = ':red: no';
      }

    my $ec_016 = '';
    if ($record->[68])
      {
	$ec_016 = ":green: $record->[69]";
      }

    else
      {
	$ec_016 = ':red: no';
      }

    my $ics_100 = '';
    if ($record->[74])
      {
	$ics_100 = ":green: $record->[75]";
      }

    else
      {
	$ics_100 = ':red: no';
      }

    my $ics_200 = '';
    if ($record->[76])
      {
	$ics_200 = ":green: $record->[77]";
      }

    else
      {
	$ics_200 = ':red: no';
      }

    my $ics_700 = '';
    if ($record->[78])
      {
	$ics_700 = ":green: $record->[79]";
      }

    else
      {
	$ics_700 = ':red: no';
      }

    my $ics_800 = '';
    if ($record->[80])
      {
	$ics_800 = ":green: $record->[81]";
      }

    else
      {
	$ics_800 = ':red: no';
      }

    print <<"END_OF_TEXT";
: Training:

: EC-001

: EC-016

: ICS-100

: ICS-200

: ICS-700

: ICS-800

---

:

$ec_001

$ec_016

$ics_100

$ics_200

$ics_700

$ics_800

---

END_OF_TEXT

    #-----------------------------------------------------------------
    # other
    #
    my $oes = '';
    if ($record->[82])
      {
	$oes = ":green: $record->[83]";
      }

    else
      {
	$oes = ':red: no';
      }

    my $cpr = '';
    if ($record->[84])
      {
	$cpr = ":green: $record->[85]";
      }

    else
      {
	$cpr = ':red: no';
      }

    my $meat = '';
    if ($record->[86])
      {
	$meat = ":green: $record->[87]";
      }

    else
      {
	$meat = ':red: no';
      }

    my $skywarn = '';
    if ($record->[88])
      {
	$skywarn = ":green: $record->[89]";
      }

    else
      {
	$skywarn = ':red: no';
      }

    my $milid = '';
    if ($record->[90])
      {
	$milid = ":green: yes";
      }

    else
      {
	$milid = ':red: no';
      }

    my $cbi = '';
    if ($record->[126])
      {
	$cbi = ":green: yes";
      }

    else
      {
	$cbi = ':red: no';
      }

    print <<"END_OF_TEXT";
: Other:

: OES

: CPR

: MEAT

: Skywarn

: Military ID

: CBI

---

:

$oes

$cpr

$meat

$skywarn

$milid

$cbi

---

END_OF_TEXT

    #-----------------------------------------------------------------
    # equipment 1
    #
    my $vhf_fixed = '';
    if ($record->[97])
      {
	$vhf_fixed = ":green: yes";
      }

    else
      {
	$vhf_fixed = ':red: no';
      }

    my $vhf_mobile = '';
    if ($record->[98])
      {
	$vhf_mobile = ":green: yes";
      }

    else
      {
	$vhf_mobile = ':red: no';
      }

    my $uhf_fixed = '';
    if ($record->[99])
      {
	$uhf_fixed = ":green: yes";
      }

    else
      {
	$uhf_fixed = ':red: no';
      }

    my $uhf_mobile = '';
    if ($record->[100])
      {
	$uhf_mobile = ":green: yes";
      }

    else
      {
	$uhf_mobile = ':red: no';
      }

    my $ssb_2m = '';
    if ($record->[101])
      {
	$ssb_2m = ":green: yes";
      }

    else
      {
	$ssb_2m = ':red: no';
      }

    my $fm_220 = '';
    if ($record->[102])
      {
	$fm_220 = ":green: yes";
      }

    else
      {
	$fm_220 = ':red: no';
      }

    print <<"END_OF_TEXT";
: Equipment 1:

: V-FM fixed

: V-FM mob

: U-FM fixed

: U-FM mob

: 2m SSB

: FM 220

---

:

$vhf_fixed

$vhf_mobile

$uhf_fixed

$uhf_mobile

$ssb_2m

$fm_220

---

END_OF_TEXT

    #-----------------------------------------------------------------
    # equipment 2
    #
    my $hf_mobile = '';
    if ($record->[93])
      {
	$hf_mobile = ":green: yes";
      }

    else
      {
	$hf_mobile = ':red: no';
      }

    my $hf_portable = '';
    if ($record->[94])
      {
	$hf_portable = ":green: yes";
      }

    else
      {
	$hf_portable = ':red: no';
      }

    my $dig_fixed = '';
    if ($record->[95])
      {
	$dig_fixed = ":green: yes";
      }

    else
      {
	$dig_fixed = ':red: no';
      }

    my $dig_portable = '';
    if ($record->[96])
      {
	$dig_portable = ":green: yes";
      }

    else
      {
	$dig_portable = ':red: no';
      }

    my $mag_mnt_ant = '';
    if ($record->[114])
      {
	$mag_mnt_ant = ":green: yes";
      }

    else
      {
	$mag_mnt_ant = ':red: no';
      }

    my $db_mag_mnt = '';
    if ($record->[115])
      {
	$db_mag_mnt = ":green: yes";
      }

    else
      {
	$db_mag_mnt = ':red: no';
      }

    print <<"END_OF_TEXT";
: Equipment 2:

: HF mob

: HF port

: Dig fixed

: Dig port

: Mag Mnt Ant

: DB Mag Mnt

---

:

$hf_mobile

$hf_portable

$dig_fixed

$dig_portable

$mag_mnt_ant

$db_mag_mnt

---

END_OF_TEXT

    #-----------------------------------------------------------------
    # equipment 3
    #
    my $port_hf_ant = '';
    if ($record->[116])
      {
	$port_hf_ant = ":green: yes";
      }

    else
      {
	$port_hf_ant = ':red: no';
      }

    my $airmail = '';
    if ($record->[118])
      {
	$airmail = ":green: yes";
      }

    else
      {
	$airmail = ':red: no';
      }

    my $hm_wx_sta = '';
    if ($record->[107])
      {
	$hm_wx_sta = ":green: yes";
      }

    else
      {
	$hm_wx_sta = ':red: no';
      }

    my $kestrel = '';
    if ($record->[106])
      {
	$kestrel = ":green: yes";
      }

    else
      {
	$kestrel = ':red: no';
      }

    my $aprs = '';
    if ($record->[103])
      {
	$aprs = ":green: yes";
      }

    else
      {
	$aprs = ':red: no';
      }

    my $gps = '';
    if ($record->[104])
      {
	$gps = ":green: yes";
      }

    else
      {
	$gps = ':red: no';
      }

    print <<"END_OF_TEXT";
: Equipment 3:

: Port HF Ant

: Airmail

: HM WX STA

: Kestrel

: APRS

: GPS

---

:

$port_hf_ant

$airmail

$hm_wx_sta

$kestrel

$aprs

$gps

---

END_OF_TEXT

    #-----------------------------------------------------------------
    # equipment 4
    #
    my $port_pkt = '';
    if ($record->[108])
      {
	$port_pkt = ":green: yes";
      }

    else
      {
	$port_pkt = ':red: no';
      }

    my $pkt_sta_247 = '';
    if ($record->[109])
      {
	$pkt_sta_247 = ":green: yes";
      }

    else
      {
	$pkt_sta_247 = ':red: no';
      }

    my $port_gen = '';
    if ($record->[117])
      {
	$port_gen = ":green: yes";
      }

    else
      {
	$port_gen = ':red: no';
      }

    my $emerg_pwr = '';
    if ($record->[112])
      {
	$emerg_pwr = ":green: yes";
      }

    else
      {
	$emerg_pwr = ':red: no';
      }

    my $fwd = '';
    if ($record->[113])
      {
	$fwd = ":green: yes";
      }

    else
      {
	$fwd = ':red: no';
      }

    my $atv = '';
    if ($record->[105])
      {
	$atv = ":green: yes";
      }

    else
      {
	$atv = ':red: no';
      }

    print <<"END_OF_TEXT";
: Equipment 4:

: Port Pkt

: 24/7 Pkt Sta

: Port Gen

: Emerg Pwr

: FWD

: ATV

---

:

$port_pkt

$pkt_sta_247

$port_gen

$emerg_pwr

$fwd

$atv

---

END_OF_TEXT

    #-----------------------------------------------------------------
    # equipment 5
    #
    my $digi = '';
    if ($record->[110])
      {
	$digi = ":green: yes";
      }

    else
      {
	$digi = ':red: no';
      }

    my $pkt_node = '';
    if ($record->[111])
      {
	$pkt_node = ":green: yes";
      }

    else
      {
	$pkt_node = ':red: no';
      }

    print <<"END_OF_TEXT";
: Equipment 5:

: Digipeater

: Pkt Node

: 

: 

: 

: 

---

:

$digi

$pkt_node

:

:

:

:

---

END_OF_TEXT

    #-----------------------------------------------------------------
    # other skills
    #
    if ( $record->[91] )
      {
	my $other = $record->[91];

	$other =~ s/{/(/g;
	$other =~ s/}/)/g;

	print "Other Skills: $other\n\n";
      }

    #-----------------------------------------------------------------
    # experience
    #
    if ( $record->[92] )
      {
	my $experience = $record->[92];

	$experience =~ s/{/(/g;
	$experience =~ s/}/)/g;

	print "Experience: $experience\n\n";
      }
  }

######################################################################

sub read_csv_file {

  my $file = shift;

  my $data = [];
  my $line = 0;

  open my $fh, "< :encoding(UTF-8)", $file or die "$file not readable";
  while (<$fh>) {
    ++ $line;
    next if $skip_line{$line};
    my $csv    = Text::CSV->new();
    my $status = $csv->parse($_);
    my $record = [ $csv->fields() ];
    push( @{$data}, $record );
  }
  close $fh;

  return $data;
}

######################################################################

sub remove_newlines {
  my $text = shift;
  $text =~ s/\r?\n/ /g;
  return $text;
}

######################################################################

sub by_order {
  lc($a->[$order - 1]) cmp lc($b->[$order - 1]);
}

######################################################################

1;
